package com.nmcc.app_adherence;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.database.Cursor;
 
 
public class MySQLiteHelper extends SQLiteOpenHelper {
 
    // Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = "mis_data";

    // Books table name
    private static final String TABLE_GPS = "GPSData";
    private static final String TABLE_PERSONS = "Persons";  

    // Books Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_HOUSEID = "HouseNumber";
    private static final String KEY_PERSONID = "PERSONID";

    private static final String[] COLUMNS = {KEY_ID,KEY_HOUSEID,KEY_PERSONID};
    
    SQLiteDatabase db99;
    File dbfile99;
    String dir99;
    
    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION); 
        //context.deleteDatabase(DATABASE_NAME);
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        // SQL statement to create book table
        String CREATE_BOOK_TABLE_GPS = "CREATE TABLE GPSData ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                "HouseNumber INTEGER, "+
                "ClusterNumber INTEGER, "+
                "HouseID TEXT, "+
                "Longitude DOUBLE, "+
                "Latitude DOUBLE, "+
                "HDOP DOUBLE, "+
                "household_id TEXT, "+
                "Placename TEXT, "+
                "HHResult TEXT, "+
                "HHResultLabel TEXT, "+
                "HHResultOther TEXT, "+
                "WasExported INTEGER, "+
                "DateTaken DATETIME, "+
                "DueForFollowUp INTEGER, "+
                "comment TEXT)";
 
        db.execSQL(CREATE_BOOK_TABLE_GPS);
        
        String CREATE_BOOK_TABLE_Persons = "CREATE TABLE Persons ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                "HouseNumber INTEGER, "+
                "ClusterNumber INTEGER, "+
                "HouseID TEXT, "+
                "Name TEXT, "+
                "RelationshipToHHHead TEXT, "+
                "RelationshipToHHHeadLabel TEXT, "+
                "household_id TEXT, "+
                "EnrolledForCohot INTEGER, "+
                "Gender INTEGER, "+
                "GenderLabel TEXT, "+
                "Treatment TEXT, "+
                "TreatmentLabel TEXT, "+
                "DayBirth INTEGER, "+
                "MonthBirth INTEGER, "+
                "YearBirth INTEGER, "+
                "Age INTEGER, "+
                "WasExported INTEGER, "+
                "DateTaken DATETIME, "+
                "BloodSlideNumA TEXT, "+
                "BloodSlideNumB TEXT, "+
                "PersonID TEXT)";
        
        // create books table
        db.execSQL(CREATE_BOOK_TABLE_Persons);
  
        String CREATE_BOOK_TABLE_Settings = "CREATE TABLE MySettings ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                "CurrentHouseNumber INTEGER, "+
                "CurrentClusterNumber INTEGER, "+
                "CurrentHouseID TEXT, "+
                "OtherSetting1 TEXT, "+
                "OtherSetting2 TEXT, "+
                "OtherSetting3 TEXT, "+
                "OtherSetting4 TEXT, "+
                "OtherSetting5 TEXT, "+
                "OtherSetting6 TEXT, "+
                "DeviceName TEXT)";
        
        // create books table
        db.execSQL(CREATE_BOOK_TABLE_Settings);
        //now add data, its better
        //if this is not done there is no addition of records in the mysqlite helper, auto putting of house details in persons does not work if this is not domne below
        String sql =
  	            "INSERT or replace INTO MySettings" +
  		"(CurrentHouseNumber, CurrentClusterNumber,CurrentHouseID, DeviceName) VALUES(1,1,'dummy','Phone" + AdditionalPhoneName() + "')"; 
    	  db.execSQL(sql);
        
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older books table if existed
    	db.execSQL("DROP TABLE IF EXISTS GPSData");
    	db.execSQL("DROP TABLE IF EXISTS Persons");
    	db.execSQL("DROP TABLE IF EXISTS MySettings");
        // create fresh books table
        this.onCreate(db);
    }
 
    //@Override
    public void DropAllTables() {
    	SQLiteDatabase db = this.getWritableDatabase();
        // Drop older books table if existed
        db.execSQL("DROP TABLE IF EXISTS GPSData");
        
        db.execSQL("DROP TABLE IF EXISTS Persons");
        
        db.execSQL("DROP TABLE IF EXISTS MySettings");
        // create fresh persons/gps table
        this.onCreate(db);
    }
    
    public int AdditionalPhoneName(){
    	Calendar c = Calendar.getInstance();
        
        int hour = c.get(Calendar.HOUR);
		   int min = c.get(Calendar.MINUTE);
		   int second = c.get(Calendar.SECOND);
		   int milisecond = c.get(Calendar.MILLISECOND);
 	   int day = c.get(Calendar.DAY_OF_MONTH);
			   int year = c.get(Calendar.YEAR);
			   int currentmonth = c.get(Calendar.MONTH);
			  //String AddInfoToPersonToday =currentmonth + day + hour + min + "-" + second;
			  int AddInfoPhoneToday =(year + currentmonth + day + hour + min + second + milisecond);

			  return AddInfoPhoneToday;
			  
    }
    
	    public void addHouseGPS(int newAs1ExistingAs2, int HouseNumber, int ClusterNumber, String HouseID, double Longitude, double Latitude, double HDOP, String household_id, String Placename,String HHResult,String HHResultOther,String HHResultLabel, String comment, String DateTaken){
  		// 1. get reference to writable DB
			// 1. get reference to writable DB
			SQLiteDatabase db = this.getWritableDatabase();

			if (newAs1ExistingAs2==2){
		        	  db.execSQL("UPDATE GPSData SET " +
		      		"HouseNumber=" + HouseNumber + 
		      		", ClusterNumber=" + ClusterNumber + 
		      		", Longitude=" + Longitude + 
		      		", Latitude=" + Latitude + 
		      		", HDOP=" + HDOP + 
		      		", household_id='" + household_id + 
		      		"', Placename='" + Placename + 
		      		"', HHResult='" + HHResult + 
		      		"', HHResultOther='" + HHResultOther + 
		      		"', HHResultLabel='" + HHResultLabel +
		      		"', DateTaken='" + DateTaken +
		      		"', comment='" + comment + "' WHERE HouseID='" + HouseID + "'");
	               }
		          else if (newAs1ExistingAs2==1){
 
		        	  String sql =
		  		            "INSERT or replace INTO GPSData" +
		  			"(HouseNumber, ClusterNumber,HouseID,Longitude," +
		  			"Latitude,HDOP,household_id,Placename,HHResult,HHResultOther,HHResultLabel,comment, WasExported, DateTaken) VALUES(" +
		  			HouseNumber + "," +
		  			ClusterNumber + ",'" +
		  			HouseID + "'," +
		  			Longitude + "," +
		  			Latitude + "," +
		  			HDOP + ",'" +
		  			household_id + "','" +
		  			Placename + "','" +
		  			HHResult + "','" +
		  			HHResultOther + "','" +
		  			HHResultLabel + "','" +
		  			comment + "',0,'" + DateTaken + "')";       
		  		                db.execSQL(sql);

		          }

		          db.close(); 			

	   }
	    
	    public void addPerson(int newAs1ExistingAs2, int HouseNumber, int ClusterNumber,String HouseID, String Name, String RelationshipToHHHead,String RelationshipToHHHeadLabel, String household_id, String EnrolledForCohot, String Treatment, String TreatmentLabel, String Gender,int DayBirth, int MonthBirth,int YearBirth,int Age, String PersonID, String DateTaken, String BloodSlideNumA, String BloodSlideNumB){
	    	SQLiteDatabase db = this.getWritableDatabase();

		          if(newAs1ExistingAs2==2){
		        	  db.execSQL("UPDATE Persons SET " +
		      		"HouseNumber=" + HouseNumber + 
		      		", ClusterNumber=" + ClusterNumber + 
		      		", HouseID='" + HouseID + 
		      		"', Name='" + Name + 
		      		"', RelationshipToHHHead='" + RelationshipToHHHead + 
		      		"', RelationshipToHHHeadLabel='" + RelationshipToHHHeadLabel + 
		      		"', household_id='" + household_id + 
		      		"', EnrolledForCohot='" + EnrolledForCohot + 
		      		"', Gender='" + Gender + 
		      		"', Treatment='" + Treatment + 
		      		"', TreatmentLabel='" + TreatmentLabel + 
		      		"', DayBirth=" + DayBirth + 
		      		", MonthBirth=" + MonthBirth + 
		      		", YearBirth=" + YearBirth + 
		      		", DateTaken='" + DateTaken + 
		      		"', BloodSlideNumA='" + BloodSlideNumA +
		      		"', BloodSlideNumB='" + BloodSlideNumB +
		      		"', Age=" + Age + " WHERE PersonID='" + PersonID + "'");
	               }
		          else if(newAs1ExistingAs2==1){
		        		String sql =
		    		            "INSERT or replace INTO Persons" +
		    			"(HouseNumber, ClusterNumber,HouseID,Name," +
		    			"RelationshipToHHHead,RelationshipToHHHeadLabel,household_id,EnrolledForCohot,Gender,Treatment,TreatmentLabel,DayBirth, MonthBirth,YearBirth, Age, PersonID, WasExported, DateTaken,BloodSlideNumA,BloodSlideNumB) VALUES(" +
		    			HouseNumber + "," +
		    			ClusterNumber + ",'" +
		    			HouseID + "','" +
		    			Name + "','" +
		    			RelationshipToHHHead + "','" +
		    			RelationshipToHHHeadLabel + "','" +
		    			household_id + "','" +
		    			EnrolledForCohot + "','" +
		    			Gender + "','" +
		    			Treatment + "','" +
		    			TreatmentLabel + "'," +
		    			DayBirth + "," +
		    			MonthBirth + "," +
		    			YearBirth + "," +
		    			Age + ",'" +
		    			PersonID + "'," +
		    			"0,'" + DateTaken + "', '" + BloodSlideNumA + "','" + BloodSlideNumB +"')";       
		  		                db.execSQL(sql);

		          }

		          db.close(); 			
	    	

        }
	    
	    
	    // Getting total Count
	    public int getGPSRecordsCount() {
	        String countQuery = "SELECT * FROM GPSData";
	        SQLiteDatabase db = this.getReadableDatabase();
	        Cursor cursor = db.rawQuery(countQuery, null);
	        cursor.close();
	 
	        return cursor.getCount();
	    }    
	    
	    
	    public void ExportGPSDataToCSV(String sFileName)
        {

     	try
     	{
     		
     		String dir99 = Environment.getExternalStorageDirectory().getPath();
			dbfile99 = new File(dir99+"/opendatakit/survey/metadata/webDb/http_localhost_8635/0000000000000001.db"); 
			db99 = SQLiteDatabase.openOrCreateDatabase(dbfile99, null);

 		
     		String outputFile =Environment.getExternalStorageDirectory() + sFileName;
    		
    		// before we open the file check to see if it already exists
    		boolean alreadyExists = new File(outputFile).exists();

     	    //FileWriter writer = new FileWriter(sFileName);
     		File root = Environment.getExternalStorageDirectory();
     		File gpxfile = new File(root, sFileName);
     		FileWriter writer = new FileWriter(gpxfile, false);//new file
     		         		
     		//count lines if already has data
			// if the file didn't already exist then we need to write out the header line
			if (!alreadyExists)
			{
        	    writer.append("House_Guid");
         	    writer.append(',');
         	    writer.append("HouseNumber");
         	    writer.append(',');
         	    writer.append("ClusterNumber");
        	    writer.append(',');             	    
        	    writer.append("DateTaken");
        	    writer.append(',');   
       	    
        	    writer.append("DateTakenTruncated");
        	    writer.append(',');   
       	    
        	            	    
         	    writer.append("PlaceName");
         	    writer.append(',');

         	    writer.append("HHResult");
        	    writer.append(',');
        	    
        	    writer.append("HHResultOther");
         	    writer.append(',');
         	   writer.append("HHResultLabel");
        	    writer.append(',');         	    
         	    writer.append("Comment");
         	    writer.append(',');
         	    writer.append("Longitude");
         	    writer.append(',');
         	    writer.append("Latitude");
         	    writer.append(',');
         	    writer.append("HDOP");
         	    writer.append(',');
         	    writer.append("household_id");
         	   writer.append(',');
         	  writer.append("DueForFollowUp");
         	    //DueForFollowUp
         	    writer.append('\n');  

			}		

     		//now add the data, same order as above, check corresponding fields

         SQLiteDatabase db = this.getReadableDatabase();

         int id;
         int  HouseNumber;
         int ClusterNumber;
         String DateTaken;
         String DateTakenTruncated="11111111111111111";
         String  HouseID;
         double Longitude;
         double  Latitude;
         int HDOP;
         String  household_id;
         String Placename;
         String HHResult;
         String HHResultOther;
         String HHResultLabel;
         String  comment;
         int WasExported;
         
         String table = "GPSData";
         String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Longitude","Latitude","HDOP","household_id","Placename","HHResult","HHResultOther","HHResultLabel","comment","WasExported","DateTaken"};
         //String where = "TAG1=? OR TAG2=? OR TAG3=? OR TAG4=? OR TAG5=?";
         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
         Cursor rs = db.query(table, columns, null, null, null, null, null);
         //Cursor rs = db.rawQuery("Select * from GPSData", null);
          if(rs.moveToFirst()){
               do{
                  
            	   //assing values 
        	  
            	   //name = c.getString(c.getColumnIndex("name"));
                   id = rs.getInt(rs.getColumnIndex("id"));
                   HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
                   ClusterNumber  = rs.getInt(rs.getColumnIndex("ClusterNumber"));
                   HouseID = rs.getString(rs.getColumnIndex("HouseID"));
                   Longitude = rs.getFloat(rs.getColumnIndex("Longitude"));
                  Latitude = rs.getDouble(rs.getColumnIndex("Latitude"));
                   HDOP  = rs.getInt(rs.getColumnIndex("HDOP"));
                   household_id = rs.getString(rs.getColumnIndex("household_id"));
                   Placename = rs.getString(rs.getColumnIndex("Placename"));  
                   HHResult = rs.getString(rs.getColumnIndex("HHResult")); 
                   HHResultOther = rs.getString(rs.getColumnIndex("HHResultOther"));
                   HHResultLabel = rs.getString(rs.getColumnIndex("HHResultLabel"));
                   comment = rs.getString(rs.getColumnIndex("comment"));
                   WasExported=rs.getInt(rs.getColumnIndex("WasExported"));
                   DateTaken=rs.getString(rs.getColumnIndex("DateTaken"));
                   
                   if (CheckIfPersonsInHouseTreatedWithDHAP(household_id)){
                	   //assing values 
                       if (HHResultLabel.equals("HOUSEHOLD PRESENT")){ //only export completed houses for now
                    	   //THIS NEEDS TO BE DONE ALSO FOR PERSONS, BUT NOT YET SO EVEN PERSONS ARE EXPORTED FROM INCOMPLETE HOUSES
    			                   if (WasExported==0 ){                 
    					                   //write into csv file
    					            	   writer.append(HouseID);
    					            	   writer.append(',');
    					            	   writer.append(String.valueOf(HouseNumber));
    					            	   writer.append(',');
    					            	   writer.append(String.valueOf(ClusterNumber));
    					            	   writer.append(',');
    					            	   writer.append(DateTaken);
    					            	   writer.append(',');	
    					            	   
    					            	   writer.append(getLast10CharactersOfChoicesAsValue(DateTaken));
    					            	   writer.append(',');	
    					            	   					            	   
    					            	   writer.append(Placename);
    					            	   writer.append(',');
    					
    					            	   writer.append(HHResult);
    					            	   writer.append(',');
    					            	   
    					            	   writer.append(HHResultOther);
    					            	   writer.append(',');
    					            	   writer.append(HHResultLabel);
    					            	   writer.append(',');   
    					            	   writer.append(comment);
    					            	   writer.append(',');
    					            	    writer.append(String.valueOf(Longitude));
    					            	   writer.append(',');
    					            	   writer.append(String.valueOf(Latitude));
    					            	   writer.append(',');
    					            	   writer.append(String.valueOf(HDOP));//no hdop
    					            	   writer.append(',');
    					
    					            	   writer.append(household_id);
    					            	   writer.append(',');
    					            	   writer.append("1");
    					            	   writer.append('\n');                       
    					                   
    					            //Do something Here with values
    					            	   
    					            	   	//now mark record as exported
    					            	   //UpdateWasGPSHouseExported(1,id);
    			                   	}
                           }
  
                   }
                                     
                 }while(rs.moveToNext());
             }
           rs.close();
           db.close();
           db99.close();
           
     	    //close the csv writer method
     	    writer.flush();
     	    writer.close();
    
     	   //SendFileViaBluetoothFromSDCardFolder(view, "GPSData.csv");
     	}
     	catch(IOException e)
     	{
     	     e.printStackTrace();
     	} 
     }
  
	    public void ExportGPSDataToCSVODKDirect(String sFileName, int GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11Travel12, String UniqueHouseIDToExport)
        {
            String folderInSDCardDestin;
	    	
     	try
     	{
 		
	    	
     	   //household new
	        
   	     if (GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11Travel12==10) 
   	     {
   	    	folderInSDCardDestin = "/opendatakit/survey/tables/household/forms/household_new/";

   		 } 
 

    	   //adherence phone 1
    	        
    	     else if (GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11Travel12==11) 
    	     {
    	    	 folderInSDCardDestin = "/opendatakit/survey/tables/household2/forms/Adherence_Phone_2/";

    		 } 

    	     else 
            {
       	 throw new IllegalArgumentException();
       	 }

     		
     		String outputFile =Environment.getExternalStorageDirectory() + folderInSDCardDestin  + sFileName;
    		
    		// before we open the file check to see if it already exists
    		boolean alreadyExists = new File(outputFile).exists();

     	    //FileWriter writer = new FileWriter(sFileName);
     		File root = Environment.getExternalStorageDirectory();
     		File gpxfile = new File(root + folderInSDCardDestin, sFileName);
     		FileWriter writer = new FileWriter(gpxfile, true);//new file, overwrite true
     		         		
     		//count lines if already has data
			// if the file didn't already exist then we need to write out the header line
			if (!alreadyExists)
			{
        	    writer.append("House_Guid");
         	    writer.append(',');
         	    writer.append("HouseNumber");
         	    writer.append(',');
         	    writer.append("ClusterNumber");
        	    writer.append(',');             	    
        	    writer.append("DateTaken");
        	    writer.append(',');  
         	    writer.append("PlaceName");
         	    writer.append(',');
 
         	    writer.append("HHResult");
        	    writer.append(',');
        	    
        	    writer.append("HHResultOther");
         	    writer.append(',');
         	   writer.append("HHResultLabel");
        	    writer.append(',');
        	             	    
         	    writer.append("Comment");
         	    writer.append(',');
         	    writer.append("Longitude");
         	    writer.append(',');
         	    writer.append("Latitude");
         	    writer.append(',');
         	    writer.append("HDOP");
         	    writer.append(',');
         	    writer.append("household_id");
         	    writer.append('\n');  

			}		

     		//now add the data, same order as above, check corresponding fields

         SQLiteDatabase db = this.getReadableDatabase();

         int id;
         int  HouseNumber;
         int ClusterNumber;
         String DateTaken;
         String  HouseID;
         double Longitude;
         double  Latitude;
         int HDOP;
         String  household_id;
         String Placename; 
         
         String HHResult; 
         String HHResultOther; 
         String HHResultLabel; 
         String  comment;

         
         String table = "GPSData";
         String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Longitude","Latitude","HDOP","household_id","Placename","HHResult","HHResultOther","HHResultLabel","comment", "DateTaken"};
         //String where = "TAG1=? OR TAG2=? OR TAG3=? OR TAG4=? OR TAG5=?";
         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
         String columnToSort="DateTaken";
         Cursor rs = db.query(table, columns, null, null, null, null, columnToSort+" DESC");
         //Cursor rs = db.rawQuery("Select * from GPSData", null);
          if(rs.moveToFirst()){
               do{
                   //assing values 
        	  
            	   //name = c.getString(c.getColumnIndex("name"));
                   id = rs.getInt(rs.getColumnIndex("id"));
                   HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
                   ClusterNumber  = rs.getInt(rs.getColumnIndex("ClusterNumber"));
                   HouseID = rs.getString(rs.getColumnIndex("HouseID"));
                   Longitude = rs.getFloat(rs.getColumnIndex("Longitude"));
                  Latitude = rs.getDouble(rs.getColumnIndex("Latitude"));
                   HDOP  = rs.getInt(rs.getColumnIndex("HDOP"));
                   household_id = rs.getString(rs.getColumnIndex("household_id"));
                   Placename = rs.getString(rs.getColumnIndex("Placename"));  
                   HHResult = rs.getString(rs.getColumnIndex("HHResult"));  
                   HHResultOther = rs.getString(rs.getColumnIndex("HHResultOther")); 
                   HHResultLabel = rs.getString(rs.getColumnIndex("HHResultLabel")); 
                   
                   comment = rs.getString(rs.getColumnIndex("comment"));
                   DateTaken = rs.getString(rs.getColumnIndex("DateTaken"));
                   
                   //if (UniqueHouseIDToExport.equals(HouseID)){//only take one house, current house
	                   //write into csv file
	            	   writer.append(HouseID);
	            	   writer.append(',');
	            	   writer.append(String.valueOf(HouseNumber));
	            	   writer.append(',');
	            	   writer.append(String.valueOf(ClusterNumber));
	            	   writer.append(',');
	            	   writer.append(DateTaken);
	            	   writer.append(',');	            	             	    
	            	   writer.append(Placename);
	            	   writer.append(',');
	            	   writer.append(HHResult);
	            	   writer.append(',');
	            	   
	            	   writer.append(HHResultOther);
	            	   writer.append(',');
	            	   writer.append(HHResultLabel);
	            	   writer.append(',');            	   
	            	   writer.append(comment);
	            	   writer.append(',');
	            	    writer.append(String.valueOf(Longitude));
	            	   writer.append(',');
	            	   writer.append(String.valueOf(Latitude));
	            	   writer.append(',');
	            	   writer.append(String.valueOf(HDOP));//no hdop
	            	   writer.append(',');
	
	            	   writer.append(household_id);
	            	   writer.append('\n');                       
                   //}
            //Do something Here with values

                 }while(rs.moveToNext());
             }
           rs.close();
           db.close();

     	    //close the csv writer method
     	    writer.flush();
     	    writer.close();
    
     	}
     	catch(IOException e)
     	{
     	     e.printStackTrace();
     	} 
     }
  	    
	    
		public void addSetting(int HouseNumber, int ClusterNumber, String HouseID, String PersonID, String StartCounterAs1stopAs2){

		// 1. get reference to writable DB
		SQLiteDatabase db = this.getWritableDatabase();
	      String table = "MySettings";
	         String[] columns = {"id"};
	         String where = "id=1";//
	         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
	         Cursor rs = db.query(table, columns, where, null, null, null, null);
	          if(rs.getColumnCount()>0){
	        	  db.execSQL("UPDATE MySettings SET " +
	      		"CurrentHouseNumber=" + HouseNumber + ", CurrentClusterNumber=" + ClusterNumber + ", CurrentHouseID='" + HouseID + "', OtherSetting1='" + PersonID + "', OtherSetting2='" + StartCounterAs1stopAs2 + "' WHERE id=1");
               }
	          else{
	        	  String sql =
	      	            "INSERT or replace INTO MySettings" +
	      		"(CurrentHouseNumber, CurrentClusterNumber,CurrentHouseID, OtherSetting1, OtherSetting2) VALUES(" +
	      		HouseNumber + "," +
	      		ClusterNumber + ",'" +
	      		HouseID +  "','" +
	      		PersonID + "','" +
	      		StartCounterAs1stopAs2 + "')";
	      		
	        	  db.execSQL(sql);
	          }

	          db.close(); 
	      }
	    
		public void UpdateDeviceName(String DeviceName){

			// 1. get reference to writable DB
			SQLiteDatabase db = this.getWritableDatabase();
		      String table = "MySettings";
		         String[] columns = {"id"};
		         //String where = "id=1";//
		         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
		         Cursor rs = db.query(table, columns, null, null, null, null, null);
		          if(rs.getColumnCount()>0){
		        	  db.execSQL("UPDATE MySettings SET " +
		      		"DeviceName='" + DeviceName + "' WHERE id=1");
	               }
		         
		          db.close(); 
		      }
		    

		
		public void addSettingPersons(String PersonID){

			// 1. get reference to writable DB
			SQLiteDatabase db = this.getWritableDatabase();
		      String table = "MySettings";
		         String[] columns = {"id"};
		         String where = "id=1";//
		         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
		         Cursor rs = db.query(table, columns, where, null, null, null, null);
		          if(rs.getColumnCount()>0){
		        	  db.execSQL("UPDATE MySettings SET " +
		      		"OtherSetting1='" + PersonID + "' WHERE id=1");
	               }
		          else{
		        	  
		          }

		          db.close(); 
		      }
		
		
		public String GetSetting(int HouseNumAs1ClusterAs2HouseholdIDAs3CurrentPersonAs4StartStopCounterAs5){
			// 1. get reference to writable DB
			SQLiteDatabase db = this.getReadableDatabase();
			String valueToReturn="0";
		      String table = "MySettings";
		         String[] columns = {"id","CurrentHouseNumber","CurrentClusterNumber","CurrentHouseID", "OtherSetting1","OtherSetting2"};
		         //String where = "id=1";//this is default so its acts as variable to be replaced
		         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
		         Cursor rs = db.query(table, columns, null, null, null, null, null);
		         if(rs.moveToFirst()){
		        	 
			         if (HouseNumAs1ClusterAs2HouseholdIDAs3CurrentPersonAs4StartStopCounterAs5==1) 
			            {
			        	 valueToReturn= Integer.toString(rs.getInt(rs.getColumnIndex("CurrentHouseNumber")));
	
			        	 }
			         else if (HouseNumAs1ClusterAs2HouseholdIDAs3CurrentPersonAs4StartStopCounterAs5==2) 
			        	 {
			        		 valueToReturn= Integer.toString(rs.getInt(rs.getColumnIndex("CurrentClusterNumber")));
	
			        	 } 
			         else if (HouseNumAs1ClusterAs2HouseholdIDAs3CurrentPersonAs4StartStopCounterAs5==3) 
			             {
			        		 valueToReturn= rs.getString(rs.getColumnIndex("CurrentHouseID"));
	
			        	 } 
			         else if (HouseNumAs1ClusterAs2HouseholdIDAs3CurrentPersonAs4StartStopCounterAs5==4) 
		             {
		        		 valueToReturn= rs.getString(rs.getColumnIndex("OtherSetting1"));

		        	 } 			         
			         else if (HouseNumAs1ClusterAs2HouseholdIDAs3CurrentPersonAs4StartStopCounterAs5==5) 
		             {
		        		 valueToReturn= rs.getString(rs.getColumnIndex("OtherSetting2"));

		        	 } 				         
			         else 
			             {
			        	 throw new IllegalArgumentException();
			        	 }
	               }  
		         
		         db.close(); 
		         return valueToReturn;

		         //return null;
		 }
		    
		public String GetSettingDeviceName(){
			// 1. get reference to writable DB
			SQLiteDatabase db = this.getReadableDatabase();
			String valueToReturn="0";
		      String table = "MySettings";
		         String[] columns = {"id","DeviceName"};
		         String where = "id=1";//this is default so its acts as variable to be replaced
		         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
		         Cursor rs = db.query(table, columns, where, null, null, null, null);
		         if(rs.moveToFirst()){

			        	 valueToReturn= Integer.toString(rs.getInt(rs.getColumnIndex("DeviceName")));
	
			      }  
		         
		         db.close(); 
		         return valueToReturn;

		         //return null;
		 }
		    
		
	    public void ExportPersonsToCSV(String sFileName)
        {

     	try
     	{
 		
     		String dir99 = Environment.getExternalStorageDirectory().getPath();
			dbfile99 = new File(dir99+"/opendatakit/survey/metadata/webDb/http_localhost_8635/0000000000000001.db"); 
			db99 = SQLiteDatabase.openOrCreateDatabase(dbfile99, null);
		   
     		
     		
     		String outputFile =Environment.getExternalStorageDirectory() + sFileName;
    		
    		// before we open the file check to see if it already exists
    		boolean alreadyExists = new File(outputFile).exists();

     	    //FileWriter writer = new FileWriter(sFileName);
     		File root = Environment.getExternalStorageDirectory();
     		File gpxfile = new File(root, sFileName);
     		FileWriter writer = new FileWriter(gpxfile, false);
     		         		
     		//count lines if already has data
			// if the file didn't already exist then we need to write out the header line
			if (!alreadyExists)
			{

        	    writer.append("House_Guid");
         	    writer.append(',');
         	    writer.append("HouseNumber");
         	    writer.append(',');
         	    writer.append("ClusterNumber");
        	    writer.append(',');   
        	    writer.append("DateTaken");
        	    writer.append(',');         	    
        	    
         	    writer.append("Name");
         	    writer.append(',');
         	    writer.append("RelationshipToHHHead");
         	    writer.append(',');
         	    writer.append("RelationshipToHHHeadLabel");
        	    writer.append(',');
         	    writer.append("household_id");
         	    writer.append(',');
         	    writer.append("EnrolledForCohot");
         	    writer.append(',');
         	    writer.append("Gender");
         	    writer.append(',');
         	   writer.append("GenderLabel");
        	    writer.append(',');         	    
         	    writer.append("DayBirth");
        	    writer.append(',');
         	    writer.append("MonthBirth");
         	    writer.append(',');
         	    writer.append("YearBirth");
        	    writer.append(',');
        	    writer.append("Age");
         	    writer.append(',');
         	    writer.append("Treatment");
        	    writer.append(',');
        	    writer.append("TreatmentLabel");
        	    writer.append(',');
         	    writer.append("PersonID"); 
         	    writer.append(',');
         	    
         	    writer.append("BloodSlideNumA");
         	    writer.append(',');
         	    writer.append("BloodSlideNumB");
         	    
         	    writer.append('\n');  

			}		

     		//now add the data, same order as above, check corresponding fields

         SQLiteDatabase db = this.getReadableDatabase();
         int id;
         int  HouseNumber;
         int ClusterNumber;
         String DateTaken;
         String  HouseID;
         String Name;
         String  RelationshipToHHHead;
         String  RelationshipToHHHeadLabel;
         String household_id;
         String  EnrolledForCohot;
         String Gender; 
         String GenderLabel; 
         int  DayBirth;
         int  MonthBirth;
         int  YearBirth;
         int  Age;
         String  Treatment;
         String  TreatmentLabel;
         String  PersonID;
         int WasExported;
         String BloodSlideNumA;
         String BloodSlideNumB;
         boolean person_wastreatedDHAP=false;
         
         String table = "Persons";
         String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Name","RelationshipToHHHead","RelationshipToHHHeadLabel","household_id","EnrolledForCohot","Gender","GenderLabel","DayBirth","MonthBirth","YearBirth","Age","Treatment","TreatmentLabel","PersonID","WasExported","DateTaken", "BloodSlideNumA", "BloodSlideNumB"};
         //String where = "TAG1=? OR TAG2=? OR TAG3=? OR TAG4=? OR TAG5=?";
         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
         Cursor rs = db.query(table, columns, null, null, null, null, null);
         //Cursor rs = db.rawQuery("Select * from GPSData", null);
          if(rs.moveToFirst()){
               do{
            	   //name = c.getString(c.getColumnIndex("name"));
                   id = rs.getInt(rs.getColumnIndex("id"));
                   
                   HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
                   ClusterNumber  = rs.getInt(rs.getColumnIndex("ClusterNumber"));
                   HouseID = rs.getString(rs.getColumnIndex("HouseID"));
                   Name = rs.getString(rs.getColumnIndex("Name"));
                   RelationshipToHHHead = rs.getString(rs.getColumnIndex("RelationshipToHHHead"));
                   RelationshipToHHHeadLabel = rs.getString(rs.getColumnIndex("RelationshipToHHHeadLabel"));
                   household_id  = rs.getString(rs.getColumnIndex("household_id"));
                   EnrolledForCohot = rs.getString(rs.getColumnIndex("EnrolledForCohot"));
                   Gender = rs.getString(rs.getColumnIndex("Gender")); 
                   GenderLabel = rs.getString(rs.getColumnIndex("GenderLabel")); 
                   DayBirth = rs.getInt(rs.getColumnIndex("DayBirth"));
                   MonthBirth = rs.getInt(rs.getColumnIndex("MonthBirth"));
                   YearBirth = rs.getInt(rs.getColumnIndex("YearBirth"));
                   Age = rs.getInt(rs.getColumnIndex("Age"));
                   Treatment = rs.getString(rs.getColumnIndex("Treatment"));
                   TreatmentLabel = rs.getString(rs.getColumnIndex("TreatmentLabel"));
                   PersonID = rs.getString(rs.getColumnIndex("PersonID"));
                   WasExported=rs.getInt(rs.getColumnIndex("WasExported"));
                   DateTaken=rs.getString(rs.getColumnIndex("DateTaken"));
                   
                   BloodSlideNumA=rs.getString(rs.getColumnIndex("BloodSlideNumA"));
                   BloodSlideNumB=rs.getString(rs.getColumnIndex("BloodSlideNumB"));
                   
                   //new check if treated with DHAP for followup
                   person_wastreatedDHAP=CheckIfPersonWasGivenDHapInMonitoring(PersonID);
                   
                   //assing values 
                   if (person_wastreatedDHAP){
                	   if (WasExported==0){
		                   //write into csv file
		            	   writer.append(HouseID);
		            	   writer.append(',');
		            	   writer.append(String.valueOf(HouseNumber));
		            	   writer.append(',');
		            	   writer.append(String.valueOf(ClusterNumber));
		            	   writer.append(',');
		            	   writer.append(DateTaken);
		            	   writer.append(',');	            	   
		            	   writer.append(Name);
		               	   writer.append(',');
		            	    writer.append(RelationshipToHHHead);
		            	   writer.append(',');
		            	   writer.append(RelationshipToHHHeadLabel);
		            	   writer.append(',');
		            	   writer.append(household_id);
		            	   writer.append(',');
		            	   writer.append(EnrolledForCohot);//no hdop
		            	   writer.append(',');
		            	   writer.append(Gender);
		            	   writer.append(',');
		            	   writer.append(getPersonGenderLabel(Gender));
		            	   writer.append(',');
		            	   writer.append(String.valueOf(DayBirth));
		            	   writer.append(',');	            	   
		            	   writer.append(String.valueOf(MonthBirth));
		            	   writer.append(',');
		            	   writer.append(String.valueOf(YearBirth));
		            	   writer.append(',');
		            	   writer.append(String.valueOf(Age));
		            	   writer.append(',');
		            	   writer.append(Treatment);//no hdop
		            	   writer.append(',');
		            	   writer.append(TreatmentLabel);//no hdop
		            	   writer.append(',');
		            	   writer.append(PersonID);//no hdop
		            	   writer.append(',');
		            	   writer.append(BloodSlideNumA);
		            	   writer.append(',');
		            	   writer.append(BloodSlideNumB);
		            	   
		            	   writer.append('\n');                       
		                  
		            //Do something Here with values
		            	   
		            	 //now mark record as exported
		            	   //UpdateWasPersonsExported(1,id);
	            	   
	                   }
	            	   
                   }
	                   
                   
                 }while(rs.moveToNext());
             }
           rs.close();
           db.close();

           db99.close();  
           
     	    //close the csv writer method
     	    writer.flush();
     	    writer.close();
    
     	}
     	catch(IOException e)
     	{
     	     e.printStackTrace();
     	} 
     }

	    public void ExportPersonsToCSVDirectToODKTestingOnly(String sFileName, int GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11Travel12, String UniqueGivenHouseID)
        {
           String folderInSDCardDestin;
           
     	try
     	{

     		 //members
     	     if (GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11Travel12==6) 
     	     {
     	    	folderInSDCardDestin = "/opendatakit/survey/tables/household_member/forms/member/";

     		 } 
  
     	     
     	    //travel
     	    else if(GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11Travel12==12) 
     	     {
     	    	folderInSDCardDestin = "/opendatakit/survey/tables/household_member_travel/forms/travel/";

     		 } 
  
     	          	     
     		 //member adherence
     	     //else if (GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11==9) 
     	     //{
     	    	//folderInSDCardDestin ="/opendatakit/survey/tables/household_member/forms/member_adherence/";

     		 //} 
     	     
     	   //nets
     	     else if (GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11Travel12==5) 
     	     {
     	    	folderInSDCardDestin = "/opendatakit/survey/tables/household_nets/forms/nets/";

     		 } 
     	             
     	        
     	   //malaria
     	     else if (GPSGiveOdkAs1DontTakeAs2PersonsGiveOdkAs3DontTakeAs4Net5Mem6Mal7Hshnew8Adhere9NewHouse10AdheHsPhone11Travel12==7) 
     	     {
     	    	folderInSDCardDestin ="/opendatakit/survey/tables/household_member/forms/malaria/";

     		 } 
  

     	     else 
             {
        	 throw new IllegalArgumentException();
        	 }
     		
     	     
            String outputFile =Environment.getExternalStorageDirectory() + folderInSDCardDestin  + sFileName;
    		
    		// before we open the file check to see if it already exists
    		boolean alreadyExists = new File(outputFile).exists();

     	    //FileWriter writer = new FileWriter(sFileName);
     		File root = Environment.getExternalStorageDirectory();
     		File gpxfile = new File(root + folderInSDCardDestin, sFileName);
     		FileWriter writer = new FileWriter(gpxfile, true);//new file, overwrite true
     		//if this procedure will be part of the program and not just testing make 
     		//above (gpxfile, true) as (gpxfile, false)
     		
     		//count lines if already has data
			// if the file didn't already exist then we need to write out the header line
			if (!alreadyExists)
			{

        	    writer.append("House_Guid");
         	    writer.append(',');
         	    writer.append("HouseNumber");
         	    writer.append(',');
         	    writer.append("ClusterNumber");
        	    writer.append(',');    
        	    writer.append("DateTaken");
        	    writer.append(',');           	    
         	    writer.append("Name");
         	    writer.append(',');
         	    writer.append("RelationshipToHHHead");
         	    writer.append(',');
         	    writer.append("RelationshipToHHHeadLabel");
        	    writer.append(',');
         	    writer.append("household_id");
         	    writer.append(',');
         	    writer.append("EnrolledForCohot");
         	    writer.append(',');
         	    writer.append("Gender");
         	    writer.append(',');
         	    writer.append("GenderLabel");
        	    writer.append(',');
        	             	    
         	    writer.append("DayBirth");
        	    writer.append(',');         	    
         	    writer.append("MonthBirth");
         	    writer.append(',');
         	    writer.append("YearBirth");
        	    writer.append(',');
        	    writer.append("Age");
         	    writer.append(',');
         	    writer.append("Treatment");
        	    writer.append(',');
        	    writer.append("TreatmentLabel");
        	    writer.append(',');
         	    writer.append("PersonID");  
         	    writer.append(',');
       	    
         	    writer.append("BloodSlideNumA");
         	    writer.append(',');
         	    writer.append("BloodSlideNumB");
         	             	    
         	    writer.append('\n');  

			}		

     		//now add the data, same order as above, check corresponding fields

         SQLiteDatabase db = this.getReadableDatabase();
         int id;
         int  HouseNumber;
         int ClusterNumber;
         String DateTaken;
         String  HouseID;
         String Name;
         String  RelationshipToHHHead;
         String  RelationshipToHHHeadLabel;
         String household_id;
         String  EnrolledForCohot;
         String Gender;   
         String GenderLabel;
         int  DayBirth;
         int  MonthBirth;
         int  YearBirth;
         int  Age;
         String  Treatment;
         String  TreatmentLabel;
         String  PersonID;
         String BloodSlideNumA;
         String BloodSlideNumB;
         
         String table = "Persons";
         String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Name","RelationshipToHHHead","RelationshipToHHHeadLabel","household_id","EnrolledForCohot","Gender","GenderLabel","DayBirth","MonthBirth","YearBirth","Age","Treatment","TreatmentLabel","PersonID","DateTaken", "BloodSlideNumA", "BloodSlideNumB"};
         //String where = "TAG1=? OR TAG2=? OR TAG3=? OR TAG4=? OR TAG5=?";
         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
         Cursor rs = db.query(table, columns, null, null, null, null, null);
         //Cursor rs = db.rawQuery("Select * from GPSData", null);
          if(rs.moveToFirst()){
               do{
            	   //name = c.getString(c.getColumnIndex("name"));
                   id = rs.getInt(rs.getColumnIndex("id"));
                   HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
                   ClusterNumber  = rs.getInt(rs.getColumnIndex("ClusterNumber"));
                   HouseID = rs.getString(rs.getColumnIndex("HouseID"));
                   Name = rs.getString(rs.getColumnIndex("Name"));
                   RelationshipToHHHead = rs.getString(rs.getColumnIndex("RelationshipToHHHead"));
                   RelationshipToHHHeadLabel = rs.getString(rs.getColumnIndex("RelationshipToHHHeadLabel"));
                   
                   household_id  = rs.getString(rs.getColumnIndex("household_id"));
                   EnrolledForCohot = rs.getString(rs.getColumnIndex("EnrolledForCohot"));
                   Gender = rs.getString(rs.getColumnIndex("Gender"));
                   GenderLabel = rs.getString(rs.getColumnIndex("GenderLabel"));
                   DayBirth = rs.getInt(rs.getColumnIndex("DayBirth"));
                   MonthBirth = rs.getInt(rs.getColumnIndex("MonthBirth"));
                   YearBirth = rs.getInt(rs.getColumnIndex("YearBirth"));
                   Age = rs.getInt(rs.getColumnIndex("Age"));
                   Treatment = rs.getString(rs.getColumnIndex("Treatment"));
                   TreatmentLabel = rs.getString(rs.getColumnIndex("TreatmentLabel"));
                   PersonID = rs.getString(rs.getColumnIndex("PersonID"));
                   DateTaken = rs.getString(rs.getColumnIndex("DateTaken"));
                   BloodSlideNumA = rs.getString(rs.getColumnIndex("BloodSlideNumA"));
                   BloodSlideNumB = rs.getString(rs.getColumnIndex("BloodSlideNumB"));
                   //assing values 
                   if (HouseID.equals(UniqueGivenHouseID)){
	                   //write into csv file
	            	   writer.append(HouseID);
	            	   writer.append(',');
	            	   writer.append(String.valueOf(HouseNumber));
	            	   writer.append(',');
	            	   writer.append(String.valueOf(ClusterNumber));
	            	   writer.append(',');
	            	   writer.append(DateTaken);
	            	   writer.append(',');	            	   
	            	   writer.append(Name);
	               	   writer.append(',');
	            	    writer.append(RelationshipToHHHead);
	            	   writer.append(',');
	            	   writer.append(RelationshipToHHHeadLabel);
	            	   writer.append(',');
	            	   writer.append(household_id);
	            	   writer.append(',');
	            	   writer.append(EnrolledForCohot);//no hdop
	            	   writer.append(',');
	            	   
	            	   writer.append(Gender);
	            	   writer.append(',');
	            	   //getPersonGenderLabel
	            	   
	            	   writer.append(getPersonGenderLabel(Gender));
	            	   writer.append(',');
	            	   
	            	   writer.append(String.valueOf(DayBirth));
	            	   writer.append(',');            	   
	            	   writer.append(String.valueOf(MonthBirth));
	            	   writer.append(',');
	            	   writer.append(String.valueOf(YearBirth));
	            	   writer.append(',');
	            	   writer.append(String.valueOf(Age));
	            	   writer.append(',');
	            	   writer.append(Treatment);//no hdop
	            	   writer.append(',');
	            	   writer.append(TreatmentLabel);//no hdop
	            	   writer.append(',');
	            	   writer.append(PersonID);//no hdop
	            	   writer.append(',');
	            	   writer.append(BloodSlideNumA);
	            	   writer.append(',');
	            	   writer.append(BloodSlideNumB);
	            	   	       	   
	            	   writer.append('\n');                       
                   }
            //Do something Here with values
                 }while(rs.moveToNext());
             }
           rs.close();
           db.close();

     	    //close the csv writer method
     	    writer.flush();
     	    writer.close();
    
     	}
     	catch(IOException e)
     	{
     	     e.printStackTrace();
     	} 
     }
    
	    
		public void UpdateWasGPSHouseExported(int IfExportedAs1, int idNumber){

		// 1. get reference to writable DB
		SQLiteDatabase db = this.getWritableDatabase();
	      String table = "GPSData";
	         String[] columns = {"id"};
	         String where = "id=" + idNumber;
	         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
	         Cursor rs = db.query(table, columns, where, null, null, null, null);
	         
	         if(rs.getColumnCount()>0){
	        	  db.execSQL("UPDATE GPSData SET " +
	      		"WasExported=" + IfExportedAs1 + " WHERE id=" + idNumber);
               }
	          else{
	        	
	          }

	          db.close(); 
	      }
		
		public void UpdateWasPersonsExported(int IfExportedAs1, int idNumber){

		// 1. get reference to writable DB
		SQLiteDatabase db = this.getWritableDatabase();
	      String table = "Persons";
	         String[] columns = {"id"};
	         String where = "id=" + idNumber;
	         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
	         Cursor rs = db.query(table, columns, where, null, null, null, null);
	          if(rs.getColumnCount()>0){
	        	  db.execSQL("UPDATE Persons SET " +
	      		"WasExported=" + IfExportedAs1 + " WHERE id=" + idNumber);
               }
	          else{
	        	
	          }

	          db.close(); 
	      }
		
				
		public String GetHouseNameAndIDForListing(int givenId){
			
			String comment;
        	String placename;
        	int housenumber;
        	int id;
        	String DateTaken;
        	
        	// 1. get reference to writable DB
        	
			SQLiteDatabase db = this.getReadableDatabase();
			String valueToReturn="0";
		      String table = "GPSData";
		         String[] columns = {"id","comment","HouseNumber","Placename", "HouseID","DateTaken"};
		         String where = "id=" + givenId;//this is default so its acts as variable to be replaced
		         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
		         Cursor rs = db.query(table, columns, where, null, null, null, null);
		         if(rs.moveToFirst()){
		        	 
			         
			        	comment= rs.getString(rs.getColumnIndex("comment"));
			        	placename= rs.getString(rs.getColumnIndex("Placename"));
			        	housenumber= rs.getInt(rs.getColumnIndex("HouseNumber"));
			        	String HouseID= rs.getString(rs.getColumnIndex("HouseID"));
			        	id= rs.getInt(rs.getColumnIndex("id"));
			        	DateTaken= rs.getString(rs.getColumnIndex("DateTaken"));
			     
			        	if (id>0)
	                    {
			        		valueToReturn= housenumber + ", " + comment + ", || " + placename + ", " + DateTaken + "                                                                                        " + HouseID;
	       	          	} 
			        
	               }  
		         
		         db.close(); 
		         return valueToReturn;

		         //return null;
		 }
		
		
		
public String GetHouseDetailsOfSelectedHouse(String givenHouseUniqueNumber, int RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14){
			
			SQLiteDatabase db = this.getReadableDatabase();
			String valueToReturn="0";
		      String table = "GPSData";
		         String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Longitude","Latitude","HDOP","household_id","Placename","HHResult","HHResultLabel","HHResultOther","comment","DateTaken"};
		         String where = "HouseID='" + givenHouseUniqueNumber + "'";//this is default so its acts as variable to be replaced
		         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
		         Cursor rs = db.query(table, columns, where, null, null, null, null);
		         if(rs.moveToFirst()){
		        	 
		        	 
		        	 if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==1){
			        	 int HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
			        	 return String.valueOf(HouseNumber);
		        	 }
		        	 
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==2){
				        	int ClusterNumber= rs.getInt(rs.getColumnIndex("ClusterNumber"));
				        	return String.valueOf(ClusterNumber);
		        	 }

		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==3){
				        	return rs.getString(rs.getColumnIndex("HouseID"));
				        
		        	 }
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==4){
				        	Double Longitude= rs.getDouble(rs.getColumnIndex("Longitude"));
				        	return String.valueOf(Longitude);
		        	 }
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==5){
		        		 Double Latitude= rs.getDouble(rs.getColumnIndex("Latitude"));
				        	return String.valueOf(Latitude);
		        	 }
		        	 		        	 
		        	 
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==6){
		        		 int HDOP= rs.getInt(rs.getColumnIndex("HDOP"));
				        	return String.valueOf(HDOP);       	 
		        	 }
		        	 
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==7){

				        	return rs.getString(rs.getColumnIndex("household_id"));

		        	 }
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==8){

				        	return rs.getString(rs.getColumnIndex("Placename"));		        	 

		        	 }
		        	 
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==9){
		        	 
				        	return rs.getString(rs.getColumnIndex("HHResult"));
		        	 }
		        	 
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==11){

				        	return rs.getString(rs.getColumnIndex("HHResultLabel"));

		        	 }
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==10){

				        	return rs.getString(rs.getColumnIndex("HHResultOther"));		        	 

		        	 }
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==12){
	        	 
				        	return rs.getString(rs.getColumnIndex("comment"));
		        	 }		        	 
		        	 
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==13){
		        	 
				        	int exported = rs.getInt(rs.getColumnIndex("WasExported"));	
				        	return String.valueOf(exported);
		        	 }		        	 
		        	 else if (RetHN1Clust2HouseID3Lon4Lat5HDOP6housid7Plac8HHResult9HHResultOther10HHResultLabel11comment12WasExported13DateAs14==14){
			        	 
				        	int exported = rs.getInt(rs.getColumnIndex("DateTaken"));	
				        	return String.valueOf(exported);
		        	 }		        	 
		     		     		     
	        	//if (id>0)
	                   // {
			        		//valueToReturn= "done";

	                    //} 
	               }  
		         
		         db.close(); 
		         return valueToReturn;

		         //return null;
		 }
		

	public int PersonsFileRecordCount(){
	
		// 1. get reference to writable DB
				SQLiteDatabase db = this.getWritableDatabase();
			      String table = "Persons";
			         String[] columns = {"id"};
			         //String where = "id=" + idNumber;
			         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
			         Cursor rs = db.query(table, columns, null, null, null, null, null);
			          int recordcountx=rs.getColumnCount();
			          db.close(); 
			          return recordcountx;
	
	 }
		

	
    public String getNow(){//
    	//set to sql format
    	  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date date = new Date();
            return dateFormat.format(date);
 
    }
    
    
    private String getPersonGenderLabel(String textvalue){
    	if (textvalue.equals("_01"))
        {
    		return "Male";
        }

    	else if (textvalue.equals("_02"))
        {
    		return "Female";
        }

    	else
        {
    		return "No Gender";
        }
    	
   }
    
	 private String getLast10CharactersOfChoicesAsValue(String gnstr) {
		 //get _01 in say 'Head    _01'	 	   last3 = str.substring(str.length() - 3);
 	       //String relat=String.valueOf(spnRelations1.getSelectedItem());
 	       //check if its first option 'Select Gender.....  _00', then return null
 	      
		 String lastcharacterUseForCompare = "0";
		 String Extractedlastcharacter = gnstr.substring(gnstr.length() - 1);
		 
		 if (lastcharacterUseForCompare==Extractedlastcharacter){
 	    	   return null;
 	      } else {
 	    		   return gnstr.substring(gnstr.length() - 10);
 	    		  
 	       }
 	    	   
	 }
    
	 
	 
	  
	public void MarkAllExportedGPSRecordsAsExported()
     {
  		//now add the data, same order as above, check corresponding fields
		//for checking in odk if members were treated with dhap
		String dir99 = Environment.getExternalStorageDirectory().getPath();
		dbfile99 = new File(dir99+"/opendatakit/survey/metadata/webDb/http_localhost_8635/0000000000000001.db"); 
		db99 = SQLiteDatabase.openOrCreateDatabase(dbfile99, null);
		//for checking in odk if members were treated with dhap
		
		
      SQLiteDatabase db = this.getReadableDatabase();

      int id;
      int  HouseNumber;
      int ClusterNumber;
      String DateTaken;
      String DateTakenTruncated="11111111111111111";
      String  HouseID;
      double Longitude;
      double  Latitude;
      int HDOP;
      String  household_id;
      String Placename;
      String HHResult;
      String HHResultOther;
      String HHResultLabel;
      String  comment;
      int WasExported;
      
      String table = "GPSData";
      String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Longitude","Latitude","HDOP","household_id","Placename","HHResult","HHResultOther","HHResultLabel","comment","WasExported","DateTaken"};
      //String where = "TAG1=? OR TAG2=? OR TAG3=? OR TAG4=? OR TAG5=?";
      //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
      Cursor rs = db.query(table, columns, null, null, null, null, null);
      //Cursor rs = db.rawQuery("Select * from GPSData", null);
       if(rs.moveToFirst()){
            do{
               
         	   //assing values 
     	  
         	   //name = c.getString(c.getColumnIndex("name"));
                id = rs.getInt(rs.getColumnIndex("id"));
                HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
                ClusterNumber  = rs.getInt(rs.getColumnIndex("ClusterNumber"));
                HouseID = rs.getString(rs.getColumnIndex("HouseID"));
                Longitude = rs.getFloat(rs.getColumnIndex("Longitude"));
               Latitude = rs.getDouble(rs.getColumnIndex("Latitude"));
                HDOP  = rs.getInt(rs.getColumnIndex("HDOP"));
                household_id = rs.getString(rs.getColumnIndex("household_id"));
                Placename = rs.getString(rs.getColumnIndex("Placename"));  
                HHResult = rs.getString(rs.getColumnIndex("HHResult")); 
                HHResultOther = rs.getString(rs.getColumnIndex("HHResultOther"));
                HHResultLabel = rs.getString(rs.getColumnIndex("HHResultLabel"));
                comment = rs.getString(rs.getColumnIndex("comment"));
                WasExported=rs.getInt(rs.getColumnIndex("WasExported"));
                DateTaken=rs.getString(rs.getColumnIndex("DateTaken"));
                
                if (CheckIfPersonsInHouseTreatedWithDHAP(household_id)){
                	 //assing values 
                    if (HHResultLabel.equals("HOUSEHOLD PRESENT")){ //only export completed houses for now
                 	   //THIS NEEDS TO BE DONE ALSO FOR PERSONS, BUT NOT YET SO EVEN PERSONS ARE EXPORTED FROM INCOMPLETE HOUSES
    			                   if (WasExported==0 ){                 
    					                   //write into csv file
    					            //Do something Here with values
    					            	   	//now mark record as exported
    					            	   UpdateWasGPSHouseExported(1,id);
    			                   	}
                        }
                }

                
              }while(rs.moveToNext());
          }
        rs.close();
        db.close();
        db99.close();
  	   //SendFileViaBluetoothFromSDCardFolder(view, "GPSData.csv");
  }

	 
	


	   public boolean CheckIfPersonWasGivenDHapInMonitoring(String given_personid) {
		   
			boolean person_treatedwithDHAP=false;
		   
		  // spnNotCompletedAdherenceHouses = (Spinner) findViewById(R.id.spnHousesNotCompletedByTestingPair);
	    	//lstvwHouseList=(ListView) findViewById(R.id.houses_listview);
	      List list = new ArrayList();
	      //add all the house get name personid
	      //for loop
	      
	      String houseid;
	      
	      int numrecs=0;
			    Cursor cursor99 = db99.rawQuery("SELECT * FROM household_member where person_id='" + given_personid + "' and treatment='_3'", null);
			    if (cursor99.moveToFirst())
			    {

			    	person_treatedwithDHAP=true;
			    }
 
			   // db99.close();
			    
			    return person_treatedwithDHAP;
		
		  
	   }	
		 


	   public boolean CheckIfPersonsInHouseTreatedWithDHAP(String given_household_id) {
		   //SQLiteDatabase db = this.getReadableDatabase();
		   
			boolean persons_treatedwithDHAP=false;
		   
		  // spnNotCompletedAdherenceHouses = (Spinner) findViewById(R.id.spnHousesNotCompletedByTestingPair);
	    	//lstvwHouseList=(ListView) findViewById(R.id.houses_listview);

	      
	      //String houseid;
	      
	      int numrecs=0;
			    Cursor cursor99 = db99.rawQuery("SELECT * FROM household_member where household_id='" + given_household_id + "' and treatment='_3'", null);
			    if (cursor99.moveToFirst())
			    {

			    	persons_treatedwithDHAP=true;
			    }

			   // db99.close();
			    
			    return persons_treatedwithDHAP;
		
		  
	   }	
		 
	  
	   
	    public void ExportGPSDataToCSVExtractFromPhone(String sFileName)
       {

    	try
    	{
		
    		String outputFile =Environment.getExternalStorageDirectory() + sFileName;
   		
   		// before we open the file check to see if it already exists
   		boolean alreadyExists = new File(outputFile).exists();

    	    //FileWriter writer = new FileWriter(sFileName);
    		File root = Environment.getExternalStorageDirectory();
    		File gpxfile = new File(root, sFileName);
    		FileWriter writer = new FileWriter(gpxfile, false);//new file
    		         		
    		//count lines if already has data
			// if the file didn't already exist then we need to write out the header line
			if (!alreadyExists)
			{
       	    writer.append("House_Guid");
        	    writer.append(',');
        	    writer.append("HouseNumber");
        	    writer.append(',');
        	    writer.append("ClusterNumber");
       	    writer.append(',');             	    
       	    writer.append("DateTaken");
       	    writer.append(',');   
      	    
       	    writer.append("DateTakenTruncated");
       	    writer.append(',');   
      	    
       	            	    
        	    writer.append("PlaceName");
        	    writer.append(',');

        	    writer.append("HHResult");
       	    writer.append(',');
       	    
       	    writer.append("HHResultOther");
        	    writer.append(',');
        	   writer.append("HHResultLabel");
       	    writer.append(',');         	    
        	    writer.append("Comment");
        	    writer.append(',');
        	    writer.append("Longitude");
        	    writer.append(',');
        	    writer.append("Latitude");
        	    writer.append(',');
        	    writer.append("HDOP/GPS Accuracy");
        	    writer.append(',');
        	    writer.append("household_id");
        	   writer.append(',');
        	  writer.append("DueForFollowUp");
        	    //DueForFollowUp
        	    writer.append('\n');  

			}		

    		//now add the data, same order as above, check corresponding fields

        SQLiteDatabase db = this.getReadableDatabase();

        int id;
        int  HouseNumber;
        int ClusterNumber;
        String DateTaken;
        String DateTakenTruncated="11111111111111111";
        String  HouseID;
        double Longitude;
        double  Latitude;
        int HDOP;
        String  household_id;
        String Placename;
        String HHResult;
        String HHResultOther;
        String HHResultLabel;
        String  comment;
        int WasExported;
        
        String table = "GPSData";
        String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Longitude","Latitude","HDOP","household_id","Placename","HHResult","HHResultOther","HHResultLabel","comment","WasExported","DateTaken"};
        //String where = "TAG1=? OR TAG2=? OR TAG3=? OR TAG4=? OR TAG5=?";
        //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
        Cursor rs = db.query(table, columns, null, null, null, null, null);
        //Cursor rs = db.rawQuery("Select * from GPSData", null);
         if(rs.moveToFirst()){
              do{
                 
           	   //assing values 
       	  
           	   //name = c.getString(c.getColumnIndex("name"));
                  id = rs.getInt(rs.getColumnIndex("id"));
                  HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
                  ClusterNumber  = rs.getInt(rs.getColumnIndex("ClusterNumber"));
                  HouseID = rs.getString(rs.getColumnIndex("HouseID"));
                  Longitude = rs.getFloat(rs.getColumnIndex("Longitude"));
                 Latitude = rs.getDouble(rs.getColumnIndex("Latitude"));
                  HDOP  = rs.getInt(rs.getColumnIndex("HDOP"));
                  household_id = rs.getString(rs.getColumnIndex("household_id"));
                  Placename = rs.getString(rs.getColumnIndex("Placename"));  
                  HHResult = rs.getString(rs.getColumnIndex("HHResult")); 
                  HHResultOther = rs.getString(rs.getColumnIndex("HHResultOther"));
                  HHResultLabel = rs.getString(rs.getColumnIndex("HHResultLabel"));
                  comment = rs.getString(rs.getColumnIndex("comment"));
                  WasExported=rs.getInt(rs.getColumnIndex("WasExported"));
                  DateTaken=rs.getString(rs.getColumnIndex("DateTaken"));
                  
                 // if (CheckIfPersonsInHouseTreatedWithDHAP(household_id)){
               	   //assing values 
                     // if (HHResultLabel.equals("HOUSEHOLD PRESENT")){ //only export completed houses for now
                   	   //THIS NEEDS TO BE DONE ALSO FOR PERSONS, BUT NOT YET SO EVEN PERSONS ARE EXPORTED FROM INCOMPLETE HOUSES
   			              //     if (WasExported==0 ){                 
   					                   //write into csv file
   					            	   writer.append(HouseID);
   					            	   writer.append(',');
   					            	   writer.append(String.valueOf(HouseNumber));
   					            	   writer.append(',');
   					            	   writer.append(String.valueOf(ClusterNumber));
   					            	   writer.append(',');
   					            	   writer.append(DateTaken);
   					            	   writer.append(',');	
   					            	   
   					            	   writer.append(getLast10CharactersOfChoicesAsValue(DateTaken));
   					            	   writer.append(',');	
   					            	   					            	   
   					            	   writer.append(Placename);
   					            	   writer.append(',');
   					
   					            	   writer.append(HHResult);
   					            	   writer.append(',');
   					            	   
   					            	   writer.append(HHResultOther);
   					            	   writer.append(',');
   					            	   writer.append(HHResultLabel);
   					            	   writer.append(',');   
   					            	   writer.append(comment);
   					            	   writer.append(',');
   					            	    writer.append(String.valueOf(Longitude));
   					            	   writer.append(',');
   					            	   writer.append(String.valueOf(Latitude));
   					            	   writer.append(',');
   					            	   writer.append(String.valueOf(HDOP));//no hdop
   					            	   writer.append(',');
   					
   					            	   writer.append(household_id);
   					            	   writer.append(',');
   					            	   writer.append("1");
   					            	   writer.append('\n');                       
   					                   
   					            //Do something Here with values
   					            	   
   					            	   	//now mark record as exported
   					            	   //UpdateWasGPSHouseExported(1,id);
   			                   	//}
                        //  }
 
                 // }
                                    
                }while(rs.moveToNext());
            }
          rs.close();
          db.close();
          
    	    //close the csv writer method
    	    writer.flush();
    	    writer.close();
   
    	   //SendFileViaBluetoothFromSDCardFolder(view, "GPSData.csv");
    	}
    	catch(IOException e)
    	{
    	     e.printStackTrace();
    	} 
    }
 
	

	    public void ExportPersonsToCSVExtractFromPhone(String sFileName)
        {

     	try
     	{

     		String outputFile =Environment.getExternalStorageDirectory() + sFileName;
    		
    		// before we open the file check to see if it already exists
    		boolean alreadyExists = new File(outputFile).exists();

     	    //FileWriter writer = new FileWriter(sFileName);
     		File root = Environment.getExternalStorageDirectory();
     		File gpxfile = new File(root, sFileName);
     		FileWriter writer = new FileWriter(gpxfile, false);
     		         		
     		//count lines if already has data
			// if the file didn't already exist then we need to write out the header line
			if (!alreadyExists)
			{

        	    writer.append("House_Guid");
         	    writer.append(',');
         	    writer.append("HouseNumber");
         	    writer.append(',');
         	    writer.append("ClusterNumber");
        	    writer.append(',');   
        	    writer.append("DateTaken");
        	    writer.append(',');         	    
        	    
         	    writer.append("Name");
         	    writer.append(',');
         	    writer.append("RelationshipToHHHead");
         	    writer.append(',');
         	    writer.append("RelationshipToHHHeadLabel");
        	    writer.append(',');
         	    writer.append("household_id");
         	    writer.append(',');
         	    writer.append("EnrolledForCohot");
         	    writer.append(',');
         	    writer.append("Gender");
         	    writer.append(',');
         	   writer.append("GenderLabel");
        	    writer.append(',');         	    
         	    writer.append("DayBirth");
        	    writer.append(',');
         	    writer.append("MonthBirth");
         	    writer.append(',');
         	    writer.append("YearBirth");
        	    writer.append(',');
        	    writer.append("Age");
         	    writer.append(',');
         	    writer.append("Treatment");
        	    writer.append(',');
        	    writer.append("TreatmentLabel");
        	    writer.append(',');
         	    writer.append("PersonID"); 
         	    writer.append(',');
         	    
         	    writer.append("BloodSlideNumA");
         	    writer.append(',');
         	    writer.append("BloodSlideNumB");
         	    
         	    writer.append('\n');  

			}		

     		//now add the data, same order as above, check corresponding fields

         SQLiteDatabase db = this.getReadableDatabase();
         int id;
         int  HouseNumber;
         int ClusterNumber;
         String DateTaken;
         String  HouseID;
         String Name;
         String  RelationshipToHHHead;
         String  RelationshipToHHHeadLabel;
         String household_id;
         String  EnrolledForCohot;
         String Gender; 
         String GenderLabel; 
         int  DayBirth;
         int  MonthBirth;
         int  YearBirth;
         int  Age;
         String  Treatment;
         String  TreatmentLabel;
         String  PersonID;
         int WasExported;
         String BloodSlideNumA;
         String BloodSlideNumB;
         boolean person_wastreatedDHAP=false;
         
         String table = "Persons";
         String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Name","RelationshipToHHHead","RelationshipToHHHeadLabel","household_id","EnrolledForCohot","Gender","GenderLabel","DayBirth","MonthBirth","YearBirth","Age","Treatment","TreatmentLabel","PersonID","WasExported","DateTaken", "BloodSlideNumA", "BloodSlideNumB"};
         //String where = "TAG1=? OR TAG2=? OR TAG3=? OR TAG4=? OR TAG5=?";
         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
         Cursor rs = db.query(table, columns, null, null, null, null, null);
         //Cursor rs = db.rawQuery("Select * from GPSData", null);
          if(rs.moveToFirst()){
               do{
            	   //name = c.getString(c.getColumnIndex("name"));
                   id = rs.getInt(rs.getColumnIndex("id"));
                   
                   HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
                   ClusterNumber  = rs.getInt(rs.getColumnIndex("ClusterNumber"));
                   HouseID = rs.getString(rs.getColumnIndex("HouseID"));
                   Name = rs.getString(rs.getColumnIndex("Name"));
                   RelationshipToHHHead = rs.getString(rs.getColumnIndex("RelationshipToHHHead"));
                   RelationshipToHHHeadLabel = rs.getString(rs.getColumnIndex("RelationshipToHHHeadLabel"));
                   household_id  = rs.getString(rs.getColumnIndex("household_id"));
                   EnrolledForCohot = rs.getString(rs.getColumnIndex("EnrolledForCohot"));
                   Gender = rs.getString(rs.getColumnIndex("Gender")); 
                   GenderLabel = rs.getString(rs.getColumnIndex("GenderLabel")); 
                   DayBirth = rs.getInt(rs.getColumnIndex("DayBirth"));
                   MonthBirth = rs.getInt(rs.getColumnIndex("MonthBirth"));
                   YearBirth = rs.getInt(rs.getColumnIndex("YearBirth"));
                   Age = rs.getInt(rs.getColumnIndex("Age"));
                   Treatment = rs.getString(rs.getColumnIndex("Treatment"));
                   TreatmentLabel = rs.getString(rs.getColumnIndex("TreatmentLabel"));
                   PersonID = rs.getString(rs.getColumnIndex("PersonID"));
                   WasExported=rs.getInt(rs.getColumnIndex("WasExported"));
                   DateTaken=rs.getString(rs.getColumnIndex("DateTaken"));
                   
                   BloodSlideNumA=rs.getString(rs.getColumnIndex("BloodSlideNumA"));
                   BloodSlideNumB=rs.getString(rs.getColumnIndex("BloodSlideNumB"));
                   
                   //new check if treated with DHAP for followup
                  // person_wastreatedDHAP=CheckIfPersonWasGivenDHapInMonitoring(PersonID);
                   
                   //assing values 
                 //  if (person_wastreatedDHAP){
                	 //  if (WasExported==0){
		                   //write into csv file
		            	   writer.append(HouseID);
		            	   writer.append(',');
		            	   writer.append(String.valueOf(HouseNumber));
		            	   writer.append(',');
		            	   writer.append(String.valueOf(ClusterNumber));
		            	   writer.append(',');
		            	   writer.append(DateTaken);
		            	   writer.append(',');	            	   
		            	   writer.append(Name);
		               	   writer.append(',');
		            	    writer.append(RelationshipToHHHead);
		            	   writer.append(',');
		            	   writer.append(RelationshipToHHHeadLabel);
		            	   writer.append(',');
		            	   writer.append(household_id);
		            	   writer.append(',');
		            	   writer.append(EnrolledForCohot);//no hdop
		            	   writer.append(',');
		            	   writer.append(Gender);
		            	   writer.append(',');
		            	   writer.append(getPersonGenderLabel(Gender));
		            	   writer.append(',');
		            	   writer.append(String.valueOf(DayBirth));
		            	   writer.append(',');	            	   
		            	   writer.append(String.valueOf(MonthBirth));
		            	   writer.append(',');
		            	   writer.append(String.valueOf(YearBirth));
		            	   writer.append(',');
		            	   writer.append(String.valueOf(Age));
		            	   writer.append(',');
		            	   writer.append(Treatment);//no hdop
		            	   writer.append(',');
		            	   writer.append(TreatmentLabel);//no hdop
		            	   writer.append(',');
		            	   writer.append(PersonID);//no hdop
		            	   writer.append(',');
		            	   writer.append(BloodSlideNumA);
		            	   writer.append(',');
		            	   writer.append(BloodSlideNumB);
		            	   
		            	   writer.append('\n');                       
		                  
		            //Do something Here with values
		            	   
		            	 //now mark record as exported
		            	  // UpdateWasPersonsExported(1,id);
	            	   
	                 //  }
	            	   
                  // }
	                   
                   
                 }while(rs.moveToNext());
             }
           rs.close();
           db.close();

         //  db99.close();  
           
     	    //close the csv writer method
     	    writer.flush();
     	    writer.close();
    
     	}
     	catch(IOException e)
     	{
     	     e.printStackTrace();
     	} 
     }



	    public void MarkPersonsAsExported()
        {

     	try
     	{
 		
     		String dir99 = Environment.getExternalStorageDirectory().getPath();
			dbfile99 = new File(dir99+"/opendatakit/survey/metadata/webDb/http_localhost_8635/0000000000000001.db"); 
			db99 = SQLiteDatabase.openOrCreateDatabase(dbfile99, null);
		   
     		//now add the data, same order as above, check corresponding fields

         SQLiteDatabase db = this.getReadableDatabase();
         int id;
         int  HouseNumber;
         int ClusterNumber;
         String DateTaken;
         String  HouseID;
         String Name;
         String  RelationshipToHHHead;
         String  RelationshipToHHHeadLabel;
         String household_id;
         String  EnrolledForCohot;
         String Gender; 
         String GenderLabel; 
         int  DayBirth;
         int  MonthBirth;
         int  YearBirth;
         int  Age;
         String  Treatment;
         String  TreatmentLabel;
         String  PersonID;
         int WasExported;
         String BloodSlideNumA;
         String BloodSlideNumB;
         boolean person_wastreatedDHAP=false;
         
         String table = "Persons";
         String[] columns = {"id","HouseNumber","ClusterNumber","HouseID","Name","RelationshipToHHHead","RelationshipToHHHeadLabel","household_id","EnrolledForCohot","Gender","GenderLabel","DayBirth","MonthBirth","YearBirth","Age","Treatment","TreatmentLabel","PersonID","WasExported","DateTaken", "BloodSlideNumA", "BloodSlideNumB"};
         //String where = "TAG1=? OR TAG2=? OR TAG3=? OR TAG4=? OR TAG5=?";
         //String[] args = {"tagname", "tagname", "tagname", "tagname", "tagname"};
         Cursor rs = db.query(table, columns, null, null, null, null, null);
         //Cursor rs = db.rawQuery("Select * from GPSData", null);
          if(rs.moveToFirst()){
               do{
            	   //name = c.getString(c.getColumnIndex("name"));
                   id = rs.getInt(rs.getColumnIndex("id"));
                   
                   HouseNumber = rs.getInt(rs.getColumnIndex("HouseNumber"));
                   ClusterNumber  = rs.getInt(rs.getColumnIndex("ClusterNumber"));
                   HouseID = rs.getString(rs.getColumnIndex("HouseID"));
                   Name = rs.getString(rs.getColumnIndex("Name"));
                   RelationshipToHHHead = rs.getString(rs.getColumnIndex("RelationshipToHHHead"));
                   RelationshipToHHHeadLabel = rs.getString(rs.getColumnIndex("RelationshipToHHHeadLabel"));
                   household_id  = rs.getString(rs.getColumnIndex("household_id"));
                   EnrolledForCohot = rs.getString(rs.getColumnIndex("EnrolledForCohot"));
                   Gender = rs.getString(rs.getColumnIndex("Gender")); 
                   GenderLabel = rs.getString(rs.getColumnIndex("GenderLabel")); 
                   DayBirth = rs.getInt(rs.getColumnIndex("DayBirth"));
                   MonthBirth = rs.getInt(rs.getColumnIndex("MonthBirth"));
                   YearBirth = rs.getInt(rs.getColumnIndex("YearBirth"));
                   Age = rs.getInt(rs.getColumnIndex("Age"));
                   Treatment = rs.getString(rs.getColumnIndex("Treatment"));
                   TreatmentLabel = rs.getString(rs.getColumnIndex("TreatmentLabel"));
                   PersonID = rs.getString(rs.getColumnIndex("PersonID"));
                   WasExported=rs.getInt(rs.getColumnIndex("WasExported"));
                   DateTaken=rs.getString(rs.getColumnIndex("DateTaken"));
                   
                   BloodSlideNumA=rs.getString(rs.getColumnIndex("BloodSlideNumA"));
                   BloodSlideNumB=rs.getString(rs.getColumnIndex("BloodSlideNumB"));
                   
                   //new check if treated with DHAP for followup
                   person_wastreatedDHAP=CheckIfPersonWasGivenDHapInMonitoring(PersonID);
                   
                   //assing values 
                   if (person_wastreatedDHAP){
                	   if (WasExported==0){
		                   //write into csv file

		            	 //now mark record as exported
		            	 UpdateWasPersonsExported(1,id);
	            	   
	                   }
	            	   
                   }
	                   
                   
                 }while(rs.moveToNext());
             }
           rs.close();
           db.close();

           db99.close();  
           
     	    //close the csv writer method
    
     	}
     	catch(SQLiteException e)
     	{
     	     e.printStackTrace();
     	} 
     }


	    
}
